#!/bin/bash
#****************************************************************#
# ScriptName:/usr/local/sbin/dbmonitor_slowquery.sh
# Create Date:2014-03-25 10:01
# Modify Date:2014-03-25 10:01
#***************************************************************#
#请查看Mysql监控ID获取server_id
server_id=11
if [ "$server_id" = "" ];then
    echo "请输入server_id"
    exit 1
fi

#config server_id
dbmonitor_server_id=$server_id #被监控端ID在网页中查找
  
#配置监控机数据库
dbmonitor_db_host="yourip"
dbmonitor_db_port=3306
dbmonitor_db_user="youruser"
dbmonitor_db_password="yourpassword"
dbmonitor_db_database="yourdb"
  
#配置被监控机
mysql_client="mysql" #mysql客户端绝对路径
mysql_host="yourip"
mysql_port=3306
mysql_user="youruser"
mysql_password="yourpassword"
  
#configslowqury慢日志位置
slowquery_dir="" #必须配置 
slowquery_long_time=1

slowquery_file=`$mysql_client -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password  -e "show variables like 'slow_query_log_file'"|grep log|awk '{print $2}'`
pt_query_digest="/usr/bin/pt-query-digest"   #被监控端pt工具位置


##### set a new slow query log ###########
tmp_log=`$mysql_client -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -e "select concat('$slowquery_dir','slowquery_',date_format(now(),'%Y%m%d%H'),'.log');"|grep log|sed -n -e '2p'`

#config mysql slowquery
#$mysql_client -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -e "set global slow_query_log=1;set global long_query_time=$slowquery_long_time;"
#$mysql_client -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -e "set global slow_query_log_file = '$tmp_log'; "


#collect mysql slowquery log into dbmonitor database
echo "aa"
$pt_query_digest --user=$dbmonitor_db_user --password=$dbmonitor_db_password --port=$dbmonitor_db_port --review h=$dbmonitor_db_host,D=$dbmonitor_db_database,t=dbmonitor_slowqueryreview  --history h=$dbmonitor_db_host,D=$dbmonitor_db_database,t=dbmonitor_slowqueryreviewhistory  --no-report --limit=100% --filter=" \$event->{add_column} = length(\$event->{arg}) and \$event->{serverid}=$dbmonitor_server_id " $slowquery_file > /tmp/dbmonitor_slowquery.log
echo "bb"

#config mysql slowquery
$mysql_client -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -e "set global slow_query_log=1;set global long_query_time=$slowquery_long_time;"
$mysql_client -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -e "set global slow_query_log_file = '$tmp_log'; "
#delete log before 7 days
cd $slowquery_dir
/bin/find ./ -name 'mysql-slow-*.log' -mtime +7|xargs rm -f ;
